import pandas as pd
import os
import xlrd  # 需要1.2.0版本的，2.0以上的版本只能读取.xls类型的文件
import csv
import json


class MetaDataInterface:
    def readExcelToDataFrameByFileName(self,excel_name):
        '''
                用于读取excel文件 并以df格式输出
                :param filePath: 
                :return: 
                '''''
        abPath = os.path.dirname(os.path.abspath(__file__))
        pathName = []
        for n in abPath.split('\\'):
            pathName.append(n)
            if n == 'flask_backend':
                break
        filePath = os.path.join('\\'.join(pathName), 'dataManagement/MetaDataBased', excel_name + '.xlsx')
        df = pd.read_excel(filePath)
        return df
    def excel_to_json(self, excel_name):
        '''
        用于读取excel文件 并已json格式输出
        :param filePath: 
        :return: 
        '''''
        abPath = os.path.dirname(os.path.abspath(__file__))
        pathName = []
        for n in abPath.split('\\'):
            pathName.append(n)
            if n == 'flask_backend':
                break
        filePath = os.path.join('\\'.join(pathName), 'dataManagement/MetaDataBased', excel_name + '.xlsx')

        try:
            fileType = filePath.split(".")[-1]
            # print(f'{filePath}\t{fileType}')
            if fileType == 'xlsx' or fileType == 'xls':
                res = []
                wb = xlrd.open_workbook(filePath)
                sh = wb.sheet_by_index(0)
                title = []
                for item in sh.row_values(0):
                    title.append(item)
                data = []
                # 实现第一行为key，剩下的为value 转为字典了
                [[data.append({title[index]: self.transfer(sh.row_values(it)[index]) for index in range(0, len(title))})] for
                 it in range(1, sh.nrows)]
                return data
            elif fileType == "csv":
                data = []
                with open(filePath) as csvfile:
                    rows = csv.reader(csvfile)  # 使用csv.reader读取csvfile中的文件
                    title = next(rows)  # 读取第一行每一列的标题
                    [[data.append({title[index]: self.transfer(it[index]) for index in range(0, len(title))})] for it in
                     rows]
                return data
            else:
                return -1
        except(EOFError):
            print("转化过程出错！")
            print(EOFError)
            return -1

    # 字符串输入，转成相应的类型
    def transfer(self, string):
        try:
            if float(string) == float(int(float(string))):
                return int(string)
            else:
                return float(string)
        except:
            pass
        return True if string.lower() == 'true' else (False if string.lower() == 'false' else string)

    # def getFileList(dbType):
    #
    #     return dblist

    def updateSrcFileManagementJson(self, ):
        '''
        该函数用于每次删除或增加数据表时 更新fileManagemeng.json文件的内容
        :return 最新fileManagemeng.json 包含当前完整的数据表条目:
        '''
        abPath = os.path.dirname(os.path.abspath(__file__))
        pathName = []
        for n in abPath.split('\\'):
            pathName.append(n)
            if n == 'flask_backend':
                break
        path = os.path.join('\\'.join(pathName), 'dataManagement/MetaDataBased')
        path_fileManagement = os.path.join('\\'.join(pathName), 'dataManagement/MetaDataBased/fileManagement.json')
        fileList = os.listdir(path)
        fileListXls = []
        fileNameList = []
        for f in fileList:
            fileType = f.split(".")[-1]
            fileName = f.split(".")[0]
            if fileType == 'xlsx' or fileType == 'xls':
                # 这里不适用id 因为太麻烦了，关联表太多，直接用文件名当id
                fileNameList.append(fileName)
                fileListXls.append({'fileName': fileName})

        df_fileName = pd.DataFrame(fileListXls)
        df_json = pd.read_json(path_fileManagement, )
        if df_json.shape[0] == 0:
            with open(path_fileManagement, 'w') as fp:
                fp.write(df_fileName.to_json())
                fp.close()
        # print(df_json)
        if df_json.shape[0] > len(fileNameList):
            # 如果删除一项
            drop_index = []
            for value in list(df_json.fileName):
                if value not in fileNameList:
                    drop_index.append(list(df_json.fileName).index(value))
            df_json = df_json.drop(drop_index, axis=0)
            df_json = df_json.reset_index(drop=True)
        if df_json.shape[0] < len(fileNameList):
            # 增添行
            insert_index = []
            for file in fileNameList:
                if file not in list(df_json.fileName):
                    insert_index.append(fileNameList.index(file))
            df_json = pd.concat([df_fileName.iloc[insert_index, :], df_json])
            df_json = df_json.reset_index(drop=True)
        with open(path_fileManagement, 'w') as fp:
            fp.write(df_json.to_json())
            fp.close()
        return df_json

    def getDatabaseList(self, ):
        '''
        获取数据库中最新文件列表
        :return:
        '''
        # path = 'flask_backend/dataManagement/dataBased/fileManagement.json'
        self.updateSrcFileManagementJson()
        abPath = os.path.dirname(os.path.abspath(__file__))
        pathName = []
        for n in abPath.split('\\'):
            pathName.append(n)
            if n == 'flask_backend':
                break
        path = os.path.join('\\'.join(pathName), 'dataManagement/MetaDataBased/fileManagement.json')

        # path = os.path.abspath('fileManagement.json')
        df_json = pd.read_json(path)

        databaseList = list(df_json.fileName)
        return databaseList

    def json_to_ecxel(self, json_list, excel_name):
        '''
        用于将输入的json保存为excel到dataManagement 文件夹
        :param json_list: 传入的json文件数组[{},{}]
        :param excel_name: 要保存的excel名称
        :return:
        '''

        abPath = os.path.dirname(os.path.abspath(__file__))
        pathName = []
        for n in abPath.split('\\'):
            pathName.append(n)
            if n == 'flask_backend':
                break
        path = os.path.join('\\'.join(pathName), 'dataManagement/MetaDataBased', excel_name + '.xlsx')
        # print(path)
        df = pd.DataFrame(json_list)
        df.to_excel(path)
        self.updateSrcFileManagementJson()

    def deletFile(self,excel_name):
        '''
        通过文件名删除excel文件
        :param excel_name: 要删除的excel文件名
        :return:
        '''
        abPath = os.path.dirname(os.path.abspath(__file__))
        pathName = []
        for n in abPath.split('\\'):
            pathName.append(n)
            if n == 'flask_backend':
                break
        deletFilePath = os.path.join('\\'.join(pathName), 'dataManagement/MetaDataBased', excel_name + '.xlsx')
        os.remove(deletFilePath)
        self.updateSrcFileManagementJson()

    def data_analysis(self,excel_name):
        '''
        输入一个数据表名，输出分析结果json
        :param excel_name: 要分析的数据表名
        :return: 分析结果json
        '''
        abPath = os.path.dirname(os.path.abspath(__file__))
        pathName = []
        for n in abPath.split('\\'):
            pathName.append(n)
            if n == 'flask_backend':
                break
        analysisFilePath = os.path.join('\\'.join(pathName), 'dataManagement/MetaDataBased', excel_name + '.xlsx')
        save_path = os.path.join('\\'.join(pathName), 'dataManagement/MetaDataBased', excel_name+'_describe' + '.xlsx')
        df = pd.read_excel(analysisFilePath)
        analysisResult_df = df.describe()
        analysisResult_df.index = ['有效值个数','平均值','标准差','最小值','25%分位数','50%分位数','75%分位数','最大值']
#         返回为json
        analysisResult_df.to_excel(save_path)
        # 打开analysisResult_df并转为json变量data
        wb = xlrd.open_workbook(save_path)
        sh = wb.sheet_by_index(0)
        title = []
        for item in sh.row_values(0):
            title.append(item)
        data = []
        # 实现第一行为key，剩下的为value 转为字典了
        [[data.append({title[index]: self.transfer(sh.row_values(it)[index]) for index in range(0, len(title))})] for
         it in range(1, sh.nrows)]
        # 删掉analysisResult_df
        os.remove(save_path)
        return data



